package code.whmmm.util.hikari.jdbcpool;


import code.whmmm.util.collection.ListUtil;
import code.whmmm.util.hikari.jdbcpool.inter.DbUtilInter;
import code.whmmm.util.hikari.jdbcpool.inter.SqlInterceptor;
import com.zaxxer.hikari.pool.HikariPool;
import org.apache.commons.beanutils.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import code.whmmm.util.collection.WrappedMap;

import java.lang.reflect.Field;
import java.sql.Date;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 创建时间: 8/23/2019 9:49 AM
 *
 * @author Zero
 */
public abstract class AbstractDbUtil implements DbUtilInter {
    /**
     * 可被继承的 日志对象
     */
    protected Logger logger = LoggerFactory.getLogger(this.getClass());
    /**
     * 连接池对象
     */
    protected HikariPool pool;

    protected final List<SqlInterceptor> interceptorList = new ArrayList<SqlInterceptor>(32);

    public AbstractDbUtil addSqlInterceptor(SqlInterceptor interceptor){
        this.interceptorList.add(interceptor);
        return this;
    }

    private AbstractDbUtil() {

    }

    /**
     * 创建 数据库工具类，可提供简单的增删改查
     * <pre>{@code
     * // 最好 使用 单例模式 或者 将 bean 交给 spring 维护，不应该 手动创建
     *
     * // 单例
     * public class DbInstance {
     *
     *     private static SimpleDbUtil dbUtil;
     *
     *     public static synchronized SimpleDbUtil getInstance() {
     *         // 示例代码
     *         HikariPool pool = null;
     *         if (dbUtil == null) {
     *             dbUtil = new SimpleDbUtil(pool);
     *         }
     *         return dbUtil;
     *     }
     * }
     * // spring
     * @Configuration
     * public class BeanManager {
     *
     *     @Bean
     *     public SimpleDbUtil createBean(){
     *         // 示例代码
     *         HikariPool pool = null;
     *         return new SimpleDbUtil(pool);
     *     }
     *
     * }
     *
     * }</pre>
     *
     * @param p 连接池
     */
    public AbstractDbUtil(HikariPool p) {
        this.pool = p;
    }


    public AbstractDbUtil(JdbcPoolConfig config) {
        this(JdbcPoolFactory.createPool(config));
    }

    /**
     * 设置 连接池.
     *
     * @param p 要注入的 HikariPool 连接池
     */
    private void setPool(HikariPool p) {
        this.pool = p;
    }


    /**
     * 获取数据库连接
     *
     * @return {@link Connection}
     */
    public abstract Connection getConn();

    /**
     * 一条 sql 执行完毕后的方法, 例如释放 ResultSet,Statement,Connection <br/>
     * <pre>
     *     // 执行次数
     *     afterSql();
     *     exceptionCallback(e)
     * </pre>
     *
     * <p>jdbc Connection 使用 releaseConn 来释放</p>
     */
    public abstract void afterSql(ResultSet rs, Statement ps, Connection cn);

    /**
     * 异常回调函数 .<br/>
     * 释放 jdbc connection 链接 , 如果产生了异常,需要 回滚
     * * <pre>
     *          // 执行次数
     *           afterSql();
     *           exceptionCallback(e)
     *      * </pre>
     *
     * @param e 抛出的异常
     */
    public abstract void exceptionCallback(Exception e);

    /**
     * 绑定 参数,里面有 判空,不要额外判空.
     *
     * @param ps     {@link PreparedStatement}
     * @param params 参数列表
     */
    private void bindParams(PreparedStatement ps, Object[] params) throws SQLException {
        if (params != null && params.length > 0) {
            for (int i = 0; i < ps.getParameterMetaData().getParameterCount(); i++) {
                ps.setObject(i + 1, params[i]);
            }
        }
    }

    /**
     * 关闭释放资源, 可继承
     *
     * @see #afterSql(ResultSet, Statement, Connection)
     */
    protected void close(ResultSet rs, Statement ps, Connection cn) {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }

        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }

        if (cn != null) {
            pool.evictConnection(cn);
        }
    }

    /**
     * 查询多条，返回 map
     *
     * @param sql sql语句
     * @return Map
     * @throws SQLException sql 异常
     */
    private List<Map<String, Object>> executeQuery(String sql) throws SQLException {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        ResultSet resultSet = null;
        PreparedStatement ps = null;
        Connection cn = null;

        Exception te = null;

        try {
            cn = getConn();
            ps = cn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            Map<String, Object> map;
            for (; resultSet.next(); resultList.add(map)) {
                map = doCreateRow(resultSet);
            }
        } catch (Exception e) {

            te = e;
            throw new RuntimeException(e);
        } finally {

            afterSql(resultSet, ps, cn);
            exceptionCallback(te);

        }
        return resultList;
    }


    private List<WrappedMap<String, Object>> executeQueryWrapMap(String sql, Object[] params) throws SQLException {
        List<WrappedMap<String, Object>> resultList = new ArrayList<WrappedMap<String, Object>>();
        ResultSet resultSet = null;
        PreparedStatement ps = null;
        Connection cn = null;

        Exception te = null;

        try {
            cn = getConn();
            ps = cn.prepareStatement(sql);

            bindParams(ps, params);

            resultSet = ps.executeQuery();
            WrappedMap<String, Object> map;
            for (; resultSet.next(); resultList.add(map)) {
                map = doCreateRow(resultSet);
            }
        } catch (Exception e) {

            te = e;
            throw new RuntimeException(e);
        } finally {
            afterSql(resultSet, ps, cn);
            exceptionCallback(te);
        }
        return resultList;
    }


    /**
     * 将执行sql的结果放在Map中
     *
     * @param resultSet 结果集
     * @return 返回值
     * @throws SQLException 集合
     */
    private WrappedMap<String, Object> doCreateRow(ResultSet resultSet) throws SQLException {
        WrappedMap<String, Object> result = new WrappedMap<String, Object>(16);
        try {
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int count = resultSetMetaData.getColumnCount();

            for (int i = 1; i <= count; i++) {
                String label = resultSetMetaData.getColumnLabel(i);
                Object value = null;
                try {
                    value = resultSet.getObject(i);
                } catch (SQLException e) {
                    if ("Zero date value prohibited".equals(e.getMessage())) {
                        SimpleDateFormat format =
                                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        java.util.Date currentDate = new java.util.Date();
                        value = format.format(currentDate);
                    }
                }
                result.put(label, value);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            throw e;
        }
        return result;
    }


    /**
     * 查询返回List
     *
     * @param sql   sql 代码
     * @param clazz 实体类
     * @return 结果
     * @throws Exception 异常
     */
    private <T> List<T> executeQuery(String sql, Class<T> clazz, Object[] params) throws Exception {
        List<T> resultList = null;
        ResultSet resultSet = null;
        PreparedStatement ps = null;
        Connection cn = null;

        Exception te = null;

        try {
            cn = getConn();
            ps = cn.prepareStatement(sql);

            bindParams(ps, params);

            resultSet = ps.executeQuery();
            resultList = this.ormEntityList(resultSet, clazz);

        } catch (Exception e) {
            logger.error("ERROR : the jdbc-connection is null !!! ");

            te = e;

            throw new RuntimeException(e);
        } finally {

            afterSql(resultSet, ps, cn);
            exceptionCallback(te);
        }
        return resultList;
    }


    /**
     * 将结果集映射到实体中并返回list
     *
     * @param rs               结果集
     * @param clazz            返回类
     * @param upperToUnderline 大写单词 转成下划线，暂时不起作用。。。
     * @return 实体集合
     * @throws Exception 异常
     */
    private <T> List<T> ormEntityList(ResultSet rs,
                                      Class<T> clazz,
                                      Field[] fields,
                                      boolean upperToUnderline) throws Exception {

        List<T> list = new ArrayList<T>();

        if (rs == null) {
            return list;
        }


        T newInstance;


        while (rs.next()) {
            newInstance = clazz.newInstance();

            Object value;

            for (Field f : fields) {

                try {
                    value = rs.getObject(f.getName());
                } catch (Exception ignoreException) {
                    // ignore resultSet.getObject Exception !
                    // 忽略 不匹配的项
                    // 必须完全匹配才可查询出
                    continue;
                }

                if (value != null) {
                    if (value instanceof Timestamp) {
                        Timestamp temp = (Timestamp) value;
                        value = new Date(temp.getTime());
                    }
                    if (value instanceof Clob) {
                        Clob cl = (Clob) value;
                        value = cl.getSubString(1, (int) cl
                                .length());
                    }

                    BeanUtils.copyProperty(newInstance,
                            f.getName(), value);

                }
            }
            list.add(newInstance);
        }
        return list;
    }


    private <T> List<T> ormEntityList(ResultSet rs, Class<T> clazz) throws Exception {
        // 获取 字段属性,注意，只能处理单层继承的情况！多次继承无效！
        Field[] fields = clazz.getDeclaredFields();

        List<Field> list = new ArrayList<Field>(Arrays.asList(fields));

        Set<String> set = new TreeSet<String>();

        for (Field field : fields) {
            set.add(field.getName());
        }

        Field[] parent = clazz.getSuperclass().getDeclaredFields();

        for (Field it : parent) {
            if (set.contains(it.getName())) {
                continue;
            }
            list.add(it);
        }

        return ormEntityList(rs, clazz, list.toArray(new Field[]{}), false);
    }


    /*++++++++ 以下为公开方法   ++++++++++*/


    /**
     * <h2>使用 queryOne 代替 !</h2>
     * <p>
     * 查询 单个,返回  {@code Map<String,Object>} <br/>
     * 会返回 null ! <br/>
     * <b></b>
     *
     * @param sql SQL语句
     * @return {@code null | Map<String,Object>}
     * @throws RuntimeException 返回结果 大于 1
     */
    @Deprecated
    public Map<String, Object> queryForOne(String sql) throws RuntimeException {
        List<Map<String, Object>> maps;
        try {
            maps = executeQuery(sql);
        } catch (SQLException e) {

            throw new RuntimeException(e.getMessage(), e);
        }
        Map<String, Object> o = null;
        if (maps.size() > 0) {

            if (maps.size() > 1) {
                throw new RuntimeException("查询错误! 期望返回 1 条数据，但是返回的结果大于 1 条!!!");
            }

            o = maps.get(0);
        }
        return o;
    }


    /**
     * 查询 单个
     *
     * @param sql    sql
     * @param params 参数列表
     * @return {@code WrappedMap<String,Object>}
     */
    @Override
    public WrappedMap<String, Object> queryOne(String sql, Object... params) {
        List<WrappedMap<String, Object>> list = queryList(sql, params);
        if (list.size() == 0) {
            return null;
        } else {
            return list.get(0);
        }
    }

    /**
     * 查询 多个结果
     *
     * @param sql    sql
     * @param params 参数列表
     * @return {@code List<WrappedMap<String,Object>>}
     */
    @Override
    public List<WrappedMap<String, Object>> queryList(String sql, Object... params) {
        List<WrappedMap<String, Object>> list = new ArrayList<WrappedMap<String, Object>>();

        try {
            return executeQueryWrapMap(sql, params);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    /**
     * 查询单条数据
     * 注意 ： 数据库中的 _ 不会 转大写 <br/>
     * 会返回 null ! <br/>
     * <b>select 字段 和 java 对象属性 需要完全对应!!!</b>
     *
     * @param sql    sql 语句
     * @param tClass 类型
     * @param <T>    类型
     * @return {@code null | 单条结果 }
     * @throws RuntimeException 返回结果大于 1
     */
    public <T> T queryForOne(String sql, Class<T> tClass, Object[] params) throws RuntimeException {
        List<T> list;
        try {
            list = executeQuery(sql, tClass, params);
        } catch (Exception e) {

            throw new RuntimeException(e.getMessage(), e);
        }


        T t = null;
        if (list != null && list.size() > 0) {

            if (list.size() > 1) {
                throw new RuntimeException("查询错误! 期望返回一条数据，但是返回的结果大于 1 条!!!");
            }

            t = list.get(0);
        }
        return t;
    }

    /**
     * 查询多条，返回  {@code List<T>} <br/>
     * 注意 ： 数据库中的 _ 不会 转大写 <br/>
     * <b>select 字段 和 java 对象属性 需要完全对应 !!!</b>
     *
     * @param sql    sql语句
     * @param tClass 返回值类型
     * @return {@code List<T>}
     */
    public <T> List<T> queryForList(String sql, Class<T> tClass, Object[] params) {
        List<T> list = new ArrayList<T>();
        try {
            list = executeQuery(sql, tClass, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * <h2> 使用 queryList 代替! </h2>
     * 执行sql ，返回 {@code List<Map<String,Object>>}
     *
     * @param sql sql 语句
     * @return {@code List<Map<String,Object>>}
     */
    @Deprecated
    public List<Map<String, Object>> queryForList(String sql) {
        List<Map<String, Object>> list;
        try {
            list = executeQuery(sql);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        return list;
    }

    /**
     * 以事务执行
     * conn
     */
    public void trans() {

    }

    /**
     * 提交 事务
     *
     * @param cn 连接
     */
    public abstract void commit(Connection cn) throws SQLException;

    /**
     * 回滚事务
     *
     * @param cn jdbc 连接
     */
    public abstract void rollback(Connection cn) throws SQLException;

    /**
     * 执行插入或修改,支持批量操作。 <br/>
     * 该方法以事务执行。<br/>
     * 批量操作 成功返回 1 !!
     * <pre>{@code
     * // 批量操作实例
     * int i = util.executeUpdate("UPDATE sys_user SET logname='abc' WHERE logname=?;" +
     *                         "UPDATE sys_user SET logname='def' WHERE logname=?",
     *                 new ArrayList<Object>() {{
     *                     add("456");  // 绑定第一个 ?
     *                     add("123");  // 绑定第二个 ?
     *                 }}
     *         );
     *
     *
     * }</pre>
     *
     * @param sql    sql 语句
     * @param params 预处理参数集合
     * @return 影响条数
     */
    @Override
    public int executeUpdate(String sql, Object... params) {
        int count = 0;
        Connection cn = null;
        PreparedStatement ps = null;

        Exception te = null;

        for (SqlInterceptor t : interceptorList) {
            sql = t.updateFilter(sql);
        }

        try {
            cn = getConn();
            ps = cn.prepareStatement(sql);

            bindParams(ps, params);

            cn.setAutoCommit(false);
            try {
                count = ps.executeUpdate();

                // 提交代码
                commit(cn);

            } catch (Exception e) {

                rollback(cn);

                te = e;

                throw new RuntimeException(e.getMessage(), e);
                //logger.error(e.getMessage(), e);
            }

        } catch (SQLException sqlException) {

            te = sqlException;

            throw new RuntimeException(sqlException.getMessage(), sqlException);
        } catch (NullPointerException ignoreNull) {

            logger.error("空指针异常，可能是连接池未返回连接!!!");

        } finally {


            afterSql(null, ps, cn);
            exceptionCallback(te);

        }
        return count;
    }


    /**
     * 批量执行插入或修改,sql 为 单条sql不变，collection 为数据集合,<br/>
     * 插入多条时,结果依然返回 1
     * <pre>{@code
     *  // 示例 , sql 语句 只有 1条， 但是 参数拥有多条
     *  util.executeBatch(
     *                 "INSERT INTO sys_user(logname) VALUE ( ? )",
     *
     *                 new ArrayList<Collection<Object>>() {{
     *                     add(new ArrayList<Object>() {{
     *                         add("123");
     *                     }});
     *
     *                     add(new ArrayList<Object>() {{
     *                         add("456");
     *                     }});
     *
     *                 }}
     *         );
     *
     *
     * }</pre>
     *
     * @param sql    sql 语句
     * @param params 参数集合
     * @return 插入条数
     */
    @Override
    public int executeBatch(String sql, Object... params) {
        int count = 0;
        Connection cn = null;
        PreparedStatement ps = null;
        //boolean bool = false;

        Exception te = null;

        try {

            cn = getConn();
            ps = cn.prepareStatement(sql);

            bindParams(ps, params);

            cn.setAutoCommit(false);

            // int[] counts = {};
            int counts = 0;
            try {

                //ps.addBatch(sql);
                //counts = ps.executeBatch();

                counts = ps.executeUpdate();

                commit(cn);
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                te = e;
                try {
                    rollback(cn);
                } catch (SQLException ex) {

                    te = ex;

                    //  logger.error(ex.getMessage(), ex);
                    throw new RuntimeException(ex.getMessage(), ex);
                }

            }


           /* for (int i : counts) {
                count += i;
            }*/
            count = counts;


        } catch (SQLException sqlException) {
            // logger.error(sqlException.getMessage(), sqlException);
            te = sqlException;
            throw new RuntimeException(sqlException.getMessage(), sqlException);
        } catch (NullPointerException ignoreNull) {

            logger.error("空指针异常，可能是连接池未返回连接!!!");

        } finally {

            afterSql(null, ps, cn);
            exceptionCallback(te);

        }
        return count;
    }

    /**
     * 执行批量sql 。<br/>
     * 以事务执行。<br/>
     * 内部 已经做了 list 分页，每次做多 50000 条，当然也可以手动处理，再调用本方法
     *
     * @param list 集合
     * @return 影响条数
     */
    public int executeBatch(List<String> list) {
        Connection cn = null;
        int count = 0;
        Statement st = null;

        Exception te = null;

        if (list == null || list.size() == 0) {
            return 0;
        }

        try {
            cn = getConn();

            try {
                cn.setAutoCommit(false);
                int max = 50000;
                int len = list.size();

                st = cn.createStatement();

                if (len <= max) {
                    for (String it : list) {
                        st.addBatch(it);
                    }

                    st.executeBatch();

                } else {
                    List<List<String>> lists = ListUtil.splitList(list, max);
                    for (List<String> strings : lists) {
                        for (String it : strings) {
                            st.addBatch(it);
                        }
                        st.executeBatch();
                    }
                }
                // 最后提交 事务
                commit(cn);
            } catch (Exception e) {
                rollback(cn);

                te = e;

                throw new RuntimeException(e.getMessage(), e);
            }

        } catch (SQLException e) {

            te = e;

            throw new RuntimeException(e.getMessage(), e);
        } catch (NullPointerException e) {

            te = e;

            logger.error("空指针异常!!!");
        } finally {

            afterSql(null, st, cn);
            exceptionCallback(te);

        }

        return count;
    }


    /*+++++++  方法别名  +++++++++*/


    public List<WrappedMap<String, Object>> queryList(String sql) {
        return queryList(sql, (Object[]) null);
    }

    public WrappedMap<String, Object> queryOne(String sql) {
        return queryOne(sql, (Object[]) null);
    }


    public <T> T queryForOne(String sql, Class<T> tClass) throws RuntimeException {
        return queryForOne(sql, tClass, null);
    }

    public <T> List<T> queryForList(String sql, Class<T> tClass) {
        return queryForList(sql, tClass, null);
    }

}
